package cnki.kg.demo;

import cnki.kg.demo.util.Neo4jUtil;
import org.junit.jupiter.api.Test;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.test.context.web.WebAppConfiguration;

import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;


@SpringBootTest
@WebAppConfiguration
public class CreateChangChengCategory {

    @Autowired
    Neo4jUtil neo4jUtil;
    @Autowired
    @Qualifier("mysqlJdbcTemplate")
    JdbcTemplate mysqlJdbcTemplate;
    private final String domain = "长城汽车_952e5b4a-b4e3-4401-80a7-0841931b8716";//长城汽车图谱

    private enum GraphNodeType {
        Conceptual("概念实体", 0),
        Entity("实体", 1),
        Property("属性", 2),
        Method("方法", 3),
        Model("模型", 4),
        Cockpit("驾驶舱", 5);

        private final String name;

        private Integer value;

        public String getName() {
            return name;
        }

        public final Integer getValue() {
            return value;
        }

        private GraphNodeType(String name, Integer value) {
            this.name = name;
            this.value = value;
        }
    }

    @Test
    public void createDetail() {
        String sql1 = "SELECT cname,projectCode,developmentStage,DesignDept,taskName from poc_gather";
        List<Map<String, Object>> dataList = mysqlJdbcTemplate.queryForList(sql1);
        if (dataList == null || dataList.size() == 0) return;
     /*   for (Map<String, Object> mp : dataList) {
            String cname=mp.get("cname").toString();
            String cypherSql = String.format("create (n:`%s`{name:'%s',alia:'%s',type:%s,sortCode:999}) return n", domain, cname, cname, GraphNodeType.Entity.getValue());
            neo4jUtil.excuteCypherSql(cypherSql);
        }
        //部门-》文档
        for (Map<String, Object> mp : dataList) {
            String cname=mp.get("cname").toString();
            String DesignDept=mp.get("DesignDept").toString();
            String LinkcypherSql = String.format("MATCH (n:`%s`),(m:`%s`) WHERE n.name='%s' AND m.name = '%s' "
                    + "merge (n)-[r:RE{name:'%s'}]->(m)" + "RETURN r", domain, domain, DesignDept, cname, "");
            neo4jUtil.excuteCypherSql(LinkcypherSql);
        }*/
        //任务-》文档
        for (Map<String, Object> mp : dataList) {
            String cname=mp.get("cname").toString();
            if(mp.get("taskName")!=null){
                String taskName=mp.get("taskName").toString();
                String LinkcypherSql = String.format("MATCH (n:`%s`),(m:`%s`) WHERE n.name='%s' AND m.name = '%s' "
                        + "merge (n)-[r:RE{name:'%s'}]->(m)" + "RETURN r", domain, domain, taskName, cname, "");
                neo4jUtil.excuteCypherSql(LinkcypherSql);
            }
        }
        //项目-》文档
        for (Map<String, Object> mp : dataList) {
            String cname=mp.get("cname").toString();
            String projectCode=mp.get("projectCode").toString();
            String developmentStage=mp.get("developmentStage").toString();
            String LinkcypherSql = String.format("MATCH (n:`%s`),(m:`%s`) WHERE n.name='%s' AND n.pname = '%s' AND m.name = '%s' "
                    + "merge (n)-[r:RE{name:'%s'}]->(m)" + "RETURN r", domain, domain, developmentStage,projectCode, cname, "");
            neo4jUtil.excuteCypherSql(LinkcypherSql);
        }
    }
    @Test
    public void createCheshenbu() {
        String cypher=String.format("match(n:`%s`) where n.name='%s' return n",domain,"车身部");
        List<HashMap<String, Object>> nodes=neo4jUtil.GetGraphNode(cypher);
        if(nodes!=null&&nodes.size()>0){
            String nodeUuid=nodes.get(0).get("uuid").toString();
            String sql1 = "SELECT `术语名称*` as name from 车身领域";
            List<Map<String, Object>> dataList = mysqlJdbcTemplate.queryForList(sql1);
            if (dataList == null || dataList.size() == 0) return;
            for (Map<String, Object> mp : dataList) {
                String name=mp.get("name").toString();
                createNodeAndLink(GraphNodeType.Entity,domain,name,"术语名称",nodeUuid);
            }
        }
    }

    @Test
    public void createDipanbu() {
        String cypher=String.format("match(n:`%s`) where n.name='%s' return n",domain,"底盘部");
        List<HashMap<String, Object>> nodes=neo4jUtil.GetGraphNode(cypher);
        if(nodes!=null&&nodes.size()>0){
            String nodeUuid=nodes.get(0).get("uuid").toString();
            String sql1 = "SELECT `术语名称*` as name from 底盘领域";
            List<Map<String, Object>> dataList = mysqlJdbcTemplate.queryForList(sql1);
            if (dataList == null || dataList.size() == 0) return;
            for (Map<String, Object> mp : dataList) {
                String name=mp.get("name").toString();
                createNodeAndLink(GraphNodeType.Entity,domain,name,"术语名称",nodeUuid);
            }
        }
    }
    @Test
    public void createZhinenglianwngbu() {
        String cypher=String.format("match(n:`%s`) where n.name='%s' return n",domain,"智能网联部");
        List<HashMap<String, Object>> nodes=neo4jUtil.GetGraphNode(cypher);
        if(nodes!=null&&nodes.size()>0){
            String nodeUuid=nodes.get(0).get("uuid").toString();
            String sql1 = "SELECT `术语名称*` as name from 智能网联领域";
            List<Map<String, Object>> dataList = mysqlJdbcTemplate.queryForList(sql1);
            if (dataList == null || dataList.size() == 0) return;
            for (Map<String, Object> mp : dataList) {
                String name=mp.get("name").toString();
                createNodeAndLink(GraphNodeType.Entity,domain,name,"术语名称",nodeUuid);
            }
        }
    }

    private HashMap<String, Object> createNodeAndLink(GraphNodeType type, String domain, String nodeName, String linkName, String pid) {
        HashMap<String, Object> nodeql = new HashMap<>();
        String cypherSql = String.format("create (n:`%s`{name:'%s',alia:'%s',type:%s,sortCode:999}) return n", domain, nodeName, nodeName, type.getValue());
        List<HashMap<String, Object>> nodesQl = neo4jUtil.GetGraphNode(cypherSql);
        if (nodesQl != null && nodesQl.size() > 0) {
            nodeql = nodesQl.get(0);
            String LinkcypherSql = String.format("MATCH (n:`%s`),(m:`%s`) WHERE id(n)=%s AND id(m) = %s "
                    + "merge (n)-[r:RE{name:'%s'}]->(m)" + "RETURN r", domain, domain, pid, nodeql.get("uuid"), linkName);
            neo4jUtil.excuteCypherSql(LinkcypherSql);
        }
        return nodeql;
    }

    private HashMap<String, Object> mergeNodeAndLink(GraphNodeType type, String domain, String nodeName, String linkName, String tableName, Integer isLeaf, String pid, String dataId) {
        HashMap<String, Object> nodeql = new HashMap<>();
        String cypherSql = String.format("merge (n:`%s`{name:'%s',alia:'%s',type:%s,sortCode:999,isLeaf:%s,tableName:'%s',fieldName:'%s',dataId:'%s'}) return n", domain, nodeName, nodeName, type.getValue(), isLeaf, tableName, linkName, dataId);
        List<HashMap<String, Object>> nodesQl = neo4jUtil.GetGraphNode(cypherSql);
        if (nodesQl != null && nodesQl.size() > 0) {
            nodeql = nodesQl.get(0);
            String LinkcypherSql = String.format("MATCH (n:`%s`),(m:`%s`) WHERE id(n)=%s AND id(m) = %s "
                    + "merge (n)-[r:RE{name:'%s'}]->(m)" + "RETURN r", domain, domain, pid, nodeql.get("uuid"), linkName);
            neo4jUtil.excuteCypherSql(LinkcypherSql);
        }
        return nodeql;
    }
    @Test
    public void getLBJAndCS(){
        String sql1 = "SELECT id,description from poc_gather";
        List<Map<String, Object>> dataList = mysqlJdbcTemplate.queryForList(sql1);
        if (dataList == null || dataList.size() == 0) return;
        for (Map<String, Object> mp : dataList) {
            String text = mp.get("description").toString();
            String id = mp.get("id").toString();
            String pat = "零部件名称：(.*?);";
            Pattern pattern = Pattern.compile(pat);
            Matcher matcher = pattern.matcher(text);
            List<String> words=new ArrayList<>();
            List<String> words2=new ArrayList<>();
            while (matcher.find()) {
                String key=matcher.group(1);
                String[] karr=key.split("\\/");
                for (String s : karr) {
                    String[] sarr=s.split("、");
                    for (String ss : sarr) {
                        String[] ssarr=ss.split(" ");
                        words.addAll(Arrays.asList(ssarr));
                    }
                }
            }
            String pat2 = "厂家：(.*?);";
            Pattern pattern2 = Pattern.compile(pat2);
            Matcher matcher2 = pattern2.matcher(text);
            while (matcher2.find()) {
                String key2=matcher2.group(1);
                String[] karr=key2.split("\\/");
                for (String s : karr) {
                    String[] sarr=s.split("、");
                    for (String ss : sarr) {
                        String[] ssarr=ss.split(" ");
                        words2.addAll(Arrays.asList(ssarr));
                    }
                }
            }
            String wordstr="";
            String wordstr2="";
            if(words!=null&&words.size()>0){
                wordstr=words.stream().distinct().collect(Collectors.joining(";"));
            }
            if(words2!=null&&words2.size()>0){
                wordstr2=words2.stream().distinct().collect(Collectors.joining(";"));
            }
            insert(wordstr,wordstr2,id);
        }



        //return mp;
    }
    private void insert(String keyword,String keyword2,String dataId){
        String sql=String.format("update poc_gather set keyword='%s',keyword2='%s' where id='%s'",keyword,keyword2,dataId);
        mysqlJdbcTemplate.execute(sql);
    }
    @Test
    public void linkk(){
        String[] dataList=new String[]{"B01","CHB121V","长城集团","工作任务"};
         for (String mp : dataList) {
            String LinkcypherSql = String.format("MATCH (n:`%s`),(m:`%s`) WHERE n.name='%s'  AND m.name = '%s' "
                    + "merge (n)-[r:RE{name:'%s'}]->(m)" + "RETURN r", domain, domain, "长城汽车", mp, "");
            neo4jUtil.excuteCypherSql(LinkcypherSql);
        }
    }
    public void dd(){
        String sql1 = "SELECT keyword,keyword1 from poc_gather";
        List<Map<String, Object>> dataList = mysqlJdbcTemplate.queryForList(sql1);
        if (dataList == null || dataList.size() == 0) return;
        for (Map<String, Object> mp : dataList) {
            String keyword="";
            String keyword1="";
            if(mp.get("keyword")!=null){
                keyword=mp.get("keyword").toString();
                String[] ks=keyword.split(";");
                for (String k : ks) {
                    String cypherSql = String.format("create (n:`%s`{name:'%s',alia:'%s',type:%s,sortCode:999}) return n", domain, k, k, GraphNodeType.Entity.getValue());
                    neo4jUtil.excuteCypherSql(cypherSql);
                    String LinkcypherSql = String.format("MATCH (n:`%s`),(m:`%s`) WHERE n.name='%s'  AND m.name = '%s' "
                            + "merge (n)-[r:RE{name:'%s'}]->(m)" + "RETURN r", domain, domain, "零部件", k, "");
                    neo4jUtil.excuteCypherSql(LinkcypherSql);
                }
            }
            if(mp.get("keyword1")!=null){
                keyword1=mp.get("keyword1").toString();
                String[] ks=keyword1.split(";");
                for (String k : ks) {
                    String cypherSql = String.format("create (n:`%s`{name:'%s',alia:'%s',type:%s,sortCode:999}) return n", domain, k, k, GraphNodeType.Entity.getValue());
                    neo4jUtil.excuteCypherSql(cypherSql);
                    String LinkcypherSql = String.format("MATCH (n:`%s`),(m:`%s`) WHERE n.name='%s'  AND m.name = '%s' "
                            + "merge (n)-[r:RE{name:'%s'}]->(m)" + "RETURN r", domain, domain, "厂商", k, "");
                    neo4jUtil.excuteCypherSql(LinkcypherSql);
                }
            }
        }
    }
    @Test
    public void dddd(){
        String sql="select count(*) ,keyword from poc_gather  GROUP BY keyword";
        List<Map<String, Object>> dataList = mysqlJdbcTemplate.queryForList(sql);
        if (dataList == null || dataList.size() == 0) return;
        List<String> alist=new ArrayList<>();
        for (Map<String, Object> mp : dataList) {
            if(mp.get("keyword")!=null){
                String keyword=mp.get("keyword").toString();
                String[] aarr=keyword.split(";");
                alist.addAll(Arrays.asList(aarr));
            }
        }
        if(alist.size()>0){
            for (String s : alist.stream().distinct().collect(Collectors.toList())) {
               String inse=String.format("insert into 零部件 (text) values('%s')",s);
               mysqlJdbcTemplate.execute(inse);
            }
        }
    }
    @Test
    public void getLBJAndCS22(){
        String sql1 = "SELECT id,cname,description from poc_gather";
        List<Map<String, Object>> dataList = mysqlJdbcTemplate.queryForList(sql1);
        if (dataList == null || dataList.size() == 0) return;
        for (Map<String, Object> mp : dataList) {
            String text = mp.get("description").toString();
            String cname = mp.get("cname").toString();
            String id = mp.get("id").toString();
            String pat = "零部件名称：(.*?);";
            Pattern pattern = Pattern.compile(pat);
            Matcher matcher = pattern.matcher(text);
            while (matcher.find()) {
                String key=matcher.group(1);
                String[] karr=key.split("\\/");
                for (String s : karr) {
                    String[] sarr=s.split("、");
                    for (String ss : sarr) {
                        String[] ssarr=ss.split(" ");
                        for (String s1 : ssarr) {
                            String cy=String.format("select CategoryCode from bd.searchnavcategorydetail where CategoryName='%s'",s1);
                            List<Map<String, Object>> cateList =mysqlJdbcTemplate.queryForList(cy);
                            for (Map<String, Object> mp2 : cateList) {
                                String code= mp2.get("CategoryCode").toString();
                                String cll=String.format("update poc_gather set categoryCode=concat(categoryCode,';','%s') where id='%s' ",code,id);
                                mysqlJdbcTemplate.execute(cll);
                            }
                        }
                    }
                }
            }
        }



        //return mp;
    }
    @Test
    public void getLBJAndCS223(){
        String sql1 = "SELECT id,cname,description from poc_gather";
        List<Map<String, Object>> dataList = mysqlJdbcTemplate.queryForList(sql1);
        if (dataList == null || dataList.size() == 0) return;
        for (Map<String, Object> mp : dataList) {
            String text = mp.get("description").toString();
            String cname = mp.get("cname").toString();
            String id = mp.get("id").toString();
            String pat = "零部件名称：(.*?);";
            Pattern pattern = Pattern.compile(pat);
            Matcher matcher = pattern.matcher(text);
            while (matcher.find()) {
                String key=matcher.group(1);
                String[] karr=key.split("\\/");
                for (String s : karr) {
                    String[] sarr=s.split("、");
                    for (String ss : sarr) {
                        String[] ssarr=ss.split(" ");
                        for (String s1 : ssarr) {
                            String[] sarr2=s1.split("\\\\");
                            for (String s2 : sarr2) {
                                String LinkcypherSql = String.format("MATCH (n:`%s`),(m:`%s`) WHERE n.name='%s'  AND m.name = '%s' "
                                        + "merge (n)-[r:RE{name:'%s'}]->(m)" + "RETURN r", domain, domain, cname, s2, "零部件");
                                neo4jUtil.excuteCypherSql(LinkcypherSql);
                            }
                        }
                    }
                }
            }
        }
    }
    @Test
    public void updateOutputCname(){
        String sql1 = "SELECT id,type,name,version,cname,outTypeCodeVersion from poc_gather";
        List<Map<String, Object>> dataList = mysqlJdbcTemplate.queryForList(sql1);
        if (dataList == null || dataList.size() == 0) return;
        for (Map<String, Object> mp : dataList) {
            String type = mp.get("type").toString();
            String cname = mp.get("cname").toString();
            String id = mp.get("id").toString();
            String version = mp.get("version").toString();
            String name = mp.get("name").toString();
            String outTypeCodeVersion = mp.get("outTypeCodeVersion").toString();
            List<Map<String, Object>> dataFilter=dataList.stream().filter(n->String.format("%s|%s|%s",type,name,version).equals(outTypeCodeVersion)).collect(Collectors.toList());
            if(dataFilter!=null&&dataFilter.size()>0){
                String aaName=dataFilter.get(0).get("cname").toString();
                String cll=String.format("update poc_gather set outputDocName='%s' where id='%s' ",aaName,id);
                mysqlJdbcTemplate.execute(cll);
            }

        }
    }
}
